import json

import requests
from bs4 import BeautifulSoup
from openpyxl import load_workbook

from api.open_account_api import OpenAccountApi
from api.recharge_api import RechargeApi
from api.reg_login_api import RegLoginApi
from common.db_util import DBUtil
from config import path_cfg


def build_account(type, session, phone):
    clear_user_info(phone)

    if type == 'open_user':
        register_account(phone, session)
    elif type == 'recharge':
        opened_user_account(phone, session)
    elif type == 'invest':
        recharged_account(phone, session)

def common_assert(respect, resp_data):
    '''
    通用断言方法
    :param cls:
    :param respect:预期结果
    :param resp_data:响应结果
    :return:
    '''
    assert respect.get('status_code') == resp_data.status_code
    assert respect.get('description') in resp_data.json().get('description')

def read_xlsx(file_name, sheet_name, start_row, end_row):
    '''
    :param file_path: xlsx文件路径
    :param sheet_name: xlsx底部的工作表名称
    :return: [(),(),(),...]
    '''

    file_path = path_cfg.BASE_DIR + '/data/' + file_name

    wb = load_workbook(file_path)
    sheet = wb[sheet_name]

    case_data = []
    while start_row <= end_row:
        tuple_data = sheet[f'B{start_row}'].value, json.loads(sheet[f'K{start_row}'].value), json.loads(sheet[f'L{start_row}'].value)
        case_data.append(tuple_data)
        start_row += 1

    return case_data

def read_json(file_name, api_name):
    '''
    读取json文件数据
    :return:[(),(),()...]
    '''
    file_path = path_cfg.BASE_DIR + '/data/' + file_name

    with open(file_path, encoding='utf-8') as f:
        json_data = json.load(f).get(api_name)

        res_list = []

        for i in json_data:
            res_list.append(tuple(i.values()))

        return res_list

def clear_user_info(phone):
    dbUtil = DBUtil(host='121.43.169.97', port=3306,
                    user='root', password='Itcast_p2p_20191228',
                    database='czbk_member')

    sql1 = f"delete i.* from mb_member_info i inner join mb_member m on m.id = i.member_id where m.phone in ('{phone}');"
    sql2 = f"delete l.* from mb_member_login_log l inner join mb_member m on m.id=l.member_id where m.phone in ('{phone}')"
    sql3 = f"delete from mb_member where phone in ('{phone}')"
    sql4 = f"delete from mb_member_register_log where phone in ('{phone}')"

    dbUtil.dml_sql(sql1)
    dbUtil.dml_sql(sql2)
    dbUtil.dml_sql(sql3)
    dbUtil.dml_sql(sql4)

# 封装初始化借款标数据的方法
def init_tender_info():
    dbUtil = DBUtil(host='121.43.169.97', port=3306,
                    user='root', password='Itcast_p2p_20191228', database="czbk_member")
    # SQL语句
    sql = '''
        delete from `czbk_member`.`mb_member` where id=869;
        delete from `czbk_member`.`mb_member_info` where id=987;
        delete from `czbk_finance`.`fn_loan_amount` where id=987;
        delete from `p2p_mock`.`p2p_account` where id=911;
        delete from `czbk_finance`.`fn_loan` where id=642;
        delete from `czbk_finance`.`fn_loan_info` where id=832;
        delete from `czbk_finance`.`fn_loan_amount_log` where id=869;
        INSERT INTO `czbk_member`.`mb_member` (`id`, `name`, `trust_account`, `password`, `email`, `phone`, `paypassword`, `count`, `register_ip`, `register_time`, `register_date`, `lastlogin_ip`, `lastlogin_time`, `lastlogin_date`, `register_type`, `role`, `group`, `group_status`, `is_realname`, `is_email`, `is_phone`, `is_video`, `is_id5`, `is_vip`, `credit_point`, `is_auto`, `pwd_attach`, `status`, `lock_time`, `vip_start_time`, `vip_end_time`, `vip_category_id`, `vip_category_ind`, `amount`, `phonestr`) VALUES ('869', '13099775533', '6021234507160403', 'eab70f4a46c640b3dfc35040bf37c803', NULL, '13099775533', NULL, '1', '1972826569', '1588835827', '2020-05-07', '1972826569', '1588835827', '2020-05-07', '1', '1', '1', '2', '1', '-1', '1', '-1', '-1', '-1', '0', '-1', '1m4U82PqPx', '1', NULL, NULL, NULL, NULL, NULL, '0.00', NULL);
        INSERT INTO `czbk_member`.`mb_member_info` (`id`, `member_id`, `member_name`, `avatar`, `nickname`, `realname`, `gender`, `birthday`, `card_id`, `qq`, `educational_background`, `marry_status`, `graduated`, `company_industry`, `company_scale`, `company_office`, `monthly_income`, `invite_money`, `hometown_province`, `hometown_city`, `hometown_area`, `hometown_post`) VALUES ('987', '869', '13099775533', NULL, NULL, '小石头', '男', '1986-11-18', '429009118611181111', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
        INSERT INTO `czbk_finance`.`fn_loan_amount` (`id`, `member_id`, `member_name`, `credit_amount`, `credit_amount_freeze`, `vouch_amount`, `vouch_amount_freeze`, `pawn_amount`, `pawn_amount_freeze`, `roam_amount`, `roam_amount_freeze`) VALUES ('987', '869', '13099775533', '200000.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000', '0.000000');
        INSERT INTO `p2p_mock`.`p2p_account` (`id`, `account`, `AvlBal`, `AcctBal`, `FrzBal`, `create_time`) VALUES ('911', '6021234507160403', '0.00', '0.00', '0.00', '2020-05-07 16:04:03');
        INSERT INTO `czbk_finance`.`fn_loan` (`id`, `ind`, `serialno`, `name`, `member_id`, `member_name`, `amount`, `credited_amount`, `progress`, `tender_count`, `category_id`, `category_type`, `repay_type`, `period`, `apr`, `sort_index`, `sort_top`, `status`, `hidden_status`, `additional_status`, `deposit_certificate`, `certificate_file_id`, `loan_repay_status`, `loan_repay_time`, `overdue_time`, `add_date`, `add_time`, `verify_time`, `reverify_time`, `add_ip`, `vouch_company_id`, `op_status`, `marker_type`) VALUES ('642', 'f5380d59791773a8abad4141ff90a5a4', '202008231111', '数据库构造借款1', '869', '13099775533', '5000.00', '0.00', '0.00', '0', '1', '1', '1', '60', '7.55', '1', NULL, '3', '1', '-1', '-1', NULL, NULL, NULL, '1640962392', '2020-05-07', '1588847800', '1588851413', NULL, '1972826569', '0', '-1', '数据库');
        INSERT INTO `czbk_finance`.`fn_loan_info` (`id`, `loan_id`, `thumbs`, `contents`, `attachment_ids`, `password`, `use`, `tender_amount_min`, `tender_amount_max`, `freeze_amount`, `freeze_amount_proportion`, `freeze_period`, `award_status`, `fail_award_status`, `award_amount`, `award_proportion`, `award_amount_total`, `validate`, `part_status`, `tender_count`, `comment_status`, `comment_count`, `is_company`, `company_name`, `vouch_company_info`, `vouch_company_pic`, `vouch_company_guaranty`, `amount_category_id`, `hits`, `cancel_admin_id`, `cancel_remark`, `cancel_time`, `verify_admin_id`, `verify_admin_name`, `verify_remark`, `verify_time`, `verify_ip`, `reverify_admin_id`, `reverify_admin_name`, `reverify_remark`, `reverify_time`, `reverify_ip`, `auto_scale`, `is_auto`, `additional_status`, `additional_apr`, `additional_name`, `additional_amount_max`, `additional_pic`, `product_process`, `information`) VALUES ('832', '642', NULL, '数据库构造借款1', 'a:0:{}', NULL, '10102', '100.00', '1000.00', NULL, '10.00', NULL, '-1', '-1', NULL, NULL, NULL, '239', NULL, '0', '-1', '0', '-1', NULL, NULL, NULL, NULL, '1', '1', NULL, NULL, NULL, '4', 'admin', '数据库构造借款1 通过', '1588851413', '1972826569', NULL, NULL, NULL, NULL, NULL, NULL, '1', '-1', '0.000000', NULL, '0.000000', NULL, NULL, NULL);
        UPDATE `czbk_finance`.`fn_loan_amount` SET `credit_amount_freeze`='100000.000000' WHERE `member_id`='869';
        INSERT INTO `czbk_finance`.`fn_loan_amount_log` (`member_id`, `member_name`, `category_id`, `type`, `amount`, `remark`, `add_time`) VALUES ('869','13099775533', 1, 4, '100000', '借款冻结', '1972826569');	
    '''
    # 执行SQL语句
    dbUtil.dml_sql(sql)

def html_parser(response):
    result = {}

    soup = BeautifulSoup(response, 'html.parser')
    result['url'] = soup.form['action']

    input_list = soup.findAll('input')

    form_data = {}
    for i in input_list:
        form_data[i["name"]] = i["value"]

    result['req_body'] = form_data
    return result

def register_account(phone, session):
    reg_login_api = RegLoginApi(session)

    reg_login_api.get_img_verify_code(1)

    body = {
        "phone": phone,
        "imgVerifyCode": "8888",
        "type": "reg"
    }
    reg_login_api.get_sms_verify_code(body)

    body = {
        "phone": phone,
        "password": "a123456",
        "verifycode": "8888",
        "phone_code": "666666",
        "dy_server": "on"
    }
    reg_login_api.user_register(body)


def opened_user_account(phone, session):
    register_account(phone, session)
    reg_login_api = RegLoginApi(session)
    open_account_api = OpenAccountApi(session)

    # 登录
    body = {
        "keywords": phone,
        "password": "a123456"
    }
    reg_login_api.user_login(body)

    # 实名认证
    body = {
        "realname": "成龙",
        "card_id": "110101200203074151"
    }
    open_account_api.real_name(body)

    # 开户
    resp = open_account_api.open_account()

    # 第三方开户
    form_data = resp.json().get('description').get('form')
    parser_data = html_parser(form_data)
    open_account_api.third_open_account(parser_data.get('url'), parser_data.get('req_body'))


def recharged_account(phone, session):
    register_account(phone, session)
    opened_user_account(phone, session)

    recharge_api = RechargeApi(session)
    recharge_api.get_img_verify_code(1)

    body = {
        "paymentType": "chinapnrTrust",
        "amount": "10000",
        "formStr": "reForm",
        "valicode": "8888"
    }

    resp = recharge_api.recharge(body)
    form_data = resp.json().get('description').get('form')
    parser_data = html_parser(form_data)
    recharge_api.third_recharge(parser_data.get('url'), parser_data.get('req_body'))


if __name__ == '__main__':
    print(read_xlsx('P2P金融项目-接口测试用例.xlsx', '登录注册', 2, 3))
    # session = requests.Session()
    # build_account('invest', session)
